package com.zzxz.practicaltrainingsys.mapper;


import com.zzxz.practicaltrainingsys.vo.ViewMember;
import com.zzxz.practicaltrainingsys.entity.Member;
import com.zzxz.practicaltrainingsys.entity.Student;
import org.apache.ibatis.annotations.*;
import com.zzxz.practicaltrainingsys.vo.ProjectTeamInfo;
import java.util.List;

@Mapper
public interface MemberMapper {

    //在小组成员信息表（member）里插入小组组号、组员学号。
    @Insert("INSERT INTO member(team_id, student_id, member_marks) VALUES(#{team_id}, #{student_id}, #{member_marks})")
    int insertMember(Member member);

    //根据小组ID查询所有组员学号
    @Select("SELECT student_id FROM member WHERE team_id = #{team_id}")
    List<String> getStudentIdsByTeamId(@Param("team_id") Integer team_id);

    //根据小组ID查询所有组员详细信息
    @Select("SELECT * FROM member m " +
            "left join student s ON m.student_id = s.student_id " +
            "WHERE m.team_id = #{team_id}")
    List<Student> getStudents(@Param("team_id") Integer team_id);


    //组长打分，根据组员的学号在小组成员信息表（member）插入组员分数
    @Update("UPDATE member SET member_marks = #{member_marks} WHERE student_id = #{student_id}")
    int updateStudentScore(Member member);


    // 根据学号在小组成员信息表（member）查询个人成绩
    @Select("SELECT member_marks FROM member WHERE student_id = #{student_id}")
    Integer getScoreById(@Param("student_id") String student_id);

    //检查某学生是否已在该小组
    @Select("SELECT COUNT(*) FROM member WHERE team_id = #{team_id} AND student_id = #{student_id}")
    int checkMemberExist(@Param("team_id") Integer team_id, @Param("student_id") Integer student_id);

    @Select("SELECT team_id FROM member WHERE student_id = #{student_id}")
    Integer getTeamIdByStudentId(@Param("student_id") Integer student_id);

    @Select("SELECT * FROM member WHERE student_id = #{student_id}")
    List<Member> findByStudentId(@Param("student_id") Integer student_id);

    @Select("SELECT * FROM member WHERE team_id = #{team_id}")
    List<Member> listByTeamId(@Param("team_id") Integer team_id);

    @Select("<script>" +
            "SELECT COUNT(*) FROM member WHERE student_id = #{student_id} AND team_id IN " +
            "<foreach collection='teamIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>" +
            "</script>")
    int countByStudentAndTeamIds(@Param("student_id") Integer student_id, @Param("teamIds") List<Integer> teamIds);

    @Select("SELECT m.member_id, m.team_id, m.student_id, m.member_marks, " +
            "s.student_name, g.grade_name, c.college_name, mj.major_name " +
            "FROM member m " +
            "LEFT JOIN student s ON m.student_id = s.student_id " +
            "LEFT JOIN grade g ON s.grade_id = g.grade_id " +
            "LEFT JOIN college c ON s.college_id = c.college_id " +
            "LEFT JOIN major mj ON s.major_id = mj.major_id " +
            "WHERE m.team_id = #{team_id}")
    List<ViewMember> listMemberInfoByTeamId(@Param("team_id") Integer team_id);

    @Select("SELECT t.leader_id, t.team_id, t.team_name, tp.topic_name, tp.topic_id, t.invite_code " +
            "FROM member m " +
            "JOIN team t ON m.team_id = t.team_id " +
            "JOIN topic tp ON t.topic_id = tp.topic_id " +
            "WHERE m.student_id = #{student_id} AND t.project_id = #{project_id} LIMIT 1")
    ProjectTeamInfo selectProjectTeamInfo(@Param("student_id") Integer student_id, @Param("project_id") Integer project_id);



}
